"""
Case Type   : Ubtree
Case Name   : alter ubtree索引(PCR)_设置表索引可见
Create At   : 2025-11-04
Owner       : xiongqin3
Description :
    1:建表;
    2:建索引时指定PCR
    3:查看索引类型
    4:插入数据
    5:查询计划
    6:设置索引可见
    7:查询计划
    8:清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、即使禁用全表扫描,仍会走全表扫描，因为索引是invisible的
    6、成功
    7、走indexscan
    8、成功
History     :
"""


import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class UstoreUbtreeCase0065(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0065'
        self.prepare_data_01 = f"drop table if exists {self.table_name} cascade;" \
                               f"create table {self.table_name} (" \
                               f"log_id bigserial primary key," \
                               f"log_time timestamp not null," \
                               f"content text," \
                               f"severity varchar(10)" \
                               f") with (storage_type=ustore);"
        self.prepare_data_02 = f"create index i_ubtree_0065 on {self.table_name} using ubtree(log_time) with (index_type='pcr') invisible;"
        self.prepare_data_03 = f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0065%';"
        self.prepare_data_04 = f"insert into {self.table_name} (log_time, content, severity) " \
                               f"select timestamp '2024-01-01 00:00:00' + (generate_series(1,1000) || ' seconds')::interval," \
                               f"'Log entry ' || generate_series(1,1000), case when random() < 0.1 then 'ERROR'" \
                               f"when random() < 0.3 then 'WARNING' else 'INFO' end;" \
                               f"analyze {self.table_name};"
        self.prepare_data_05 = f"select count(*) from {self.table_name} where log_time between '2024-01-01' and '2024-01-31';" \
                               f"set enable_seqscan = off;" \
                               f"explain (costs off) select * from {self.table_name} where log_time between '2024-01-01' and '2024-01-31';"
        self.prepare_data_06 = f"alter index if exists i_ubtree_0065 visible;" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0065%';"
        self.prepare_data_07 = f"select count(*) from {self.table_name} where log_time between '2024-01-01' and '2024-01-31';" \
                               f"set enable_seqscan = off;" \
                               f"explain (costs off) select * from {self.table_name} where log_time between '2024-01-01' and '2024-01-31';"
        self.prepare_data_08 = f"drop table if exists {self.table_name} cascade;" \
                               f"set enable_seqscan = on;"
        
    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:查看索引类型;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)

        text = '--step4:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.ANALYZE_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step5:查询计划;expect:即使禁用全表扫描,仍会走全表扫描，因为索引是invisible的--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(" 1000\n", data_cmd, '执行失败' + text)
        self.assertIn("Seq Scan on t_ubtree_0065", data_cmd, '执行失败' + text)

        text = '--step6:设置索引可见;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn(self.constant.ALTER_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)

        text = '--step7:查询计划;expect:走indexscan--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertIn(" 1000\n", data_cmd, '执行失败' + text)
        self.assertIn("Index Scan using i_ubtree_0065 on t_ubtree_0065", data_cmd, '执行失败' + text)
        
    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')

